﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using AioNightly.Model;
using MySql.Data.MySqlClient;
using AioNightly.Common;

namespace AioNightly.Dao
{
    public class NewnoteDAO
    {
        private void Read2List(MySqlDataReader reader, List<NewNote> list)
        {
            while (reader.Read())
            {
                NewNote it = new NewNote();
                it.Id = reader.GetInt32(0);
                it.Type = reader.GetInt32(1);
                it.Content = reader.GetString(2);
                it.AuthorId = reader.GetInt32(3);
                it.CreateTime = reader.GetString(4);
                it.State = reader.GetInt32(5);
                it.ProductId = reader.GetInt32(6);

                list.Add(it);
            }
        }
        
        public List<NewNote> GetNewnoteByProduct(string productName)
        {
            DBConnect db = new DBConnect();
            db.OpenConnection();

            List<NewNote> list = new List<NewNote>();
            string sql = string.Format("SELECT  * FROM newnote,product where newnote.ProductId=product.Id and product.ProductName='{0}'", productName);
            MySqlCommand cmd = new MySqlCommand(sql, db.Conn);
            MySqlDataReader reader = cmd.ExecuteReader();

            Read2List(reader, list);

            if (reader != null)
                reader.Close();
            db.CloseConnection();

            return list;
        }

        public int AddNewnote(int type, string content, int productId)
        {
            DBConnect db = new DBConnect();
            db.OpenConnection();

            string sql = string.Format("insert into newnote (Type, Content, AuthorId, CreateTime, State, ProductId) values ({0}, '{1}', {2}, '{3}', {4}, {5})", type, content, 1, TimeManager.GetSystemTime(), (int)TypeManager.ItemState.Unchecked, productId);
            MySqlCommand cmd = new MySqlCommand(sql, db.Conn);
            int result = cmd.ExecuteNonQuery();

            return result;
        }

        public NewNote GetItemByID(int id)
        {
            DBConnect db = new DBConnect();
            db.OpenConnection();

            List<NewNote> list = new List<NewNote>();
            string sql = string.Format("SELECT  * FROM newnote where newnote.Id={0}", id);
            MySqlCommand cmd = new MySqlCommand(sql, db.Conn);
            MySqlDataReader reader = cmd.ExecuteReader();

            Read2List(reader, list);

            if (reader != null)
                reader.Close();
            db.CloseConnection();

            if (list.Count == 1)
            {
                return list[0];
            }
            else
            {
                return null;
            }
        }

        public int EditNewitemContent(int id, string content)
        {
            DBConnect db = new DBConnect();
            db.OpenConnection();

            string sql = string.Format("update newnote set Content='{1}' where Id={0}", id, content);
            MySqlCommand cmd = new MySqlCommand(sql, db.Conn);
            int result = cmd.ExecuteNonQuery();

            return result;
        }

        public int DeleNewitem(int id)
        {
            DBConnect db = new DBConnect();
            db.OpenConnection();

            string sql = string.Format("delete from newnote where Id={0}", id);
            MySqlCommand cmd = new MySqlCommand(sql, db.Conn);
            int result = cmd.ExecuteNonQuery();

            return result;
        }

        public int Move2Item(string productName)
        {
            DBConnect db = new DBConnect();
            db.OpenConnection();

            string sql = string.Format("insert into item (item.type,item.content,item.authorid,item.createtime,item.state) select newnote.type,newnote.content,newnote.authorid,newnote.createtime,newnote.state from newnote,product where newnote.productid=product.id and product.productname='{0}'", productName);
            MySqlCommand cmd = new MySqlCommand(sql, db.Conn);
            int result = cmd.ExecuteNonQuery();

            return result;
        }

        public int ClearNewnoteByProductId(int productId)
        {
            DBConnect db = new DBConnect();
            db.OpenConnection();

            string sql = string.Format("delete from newnote where newnote.productid = {0}", productId);
            MySqlCommand cmd = new MySqlCommand(sql, db.Conn);
            int result = cmd.ExecuteNonQuery();

            return result;
        }
    }
}